package com.avilyne.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.avilyne.rest.model.Book;
import com.avilyne.rest.model.Store;

public class DbBean {
	protected Connection con;
	// public static final String JDBC_DRIVER_NAME =
	// "sun.jdbc.odbc.JdbcOdbcDriver";
	// public static final String MICROSOFT_ACCESS_DATA_SOURCE_NAME =
	// "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\DB.mdb;";
	public static final String JDBC_DRIVER_NAME = "com.mysql.jdbc.Driver";
	public static final String DATA_SOURCE_NAME = "jdbc:mysql://127.0.0.1:3306/android_book";
	public static final String USER = "hellotab";
	public static final String PASSWORD = "123456";

	public DbBean() {

	}

	public void connectiondb() {
		try {
			// System.out.println("connection DB MySQL");
			Class.forName(JDBC_DRIVER_NAME);
			con = DriverManager.getConnection(DATA_SOURCE_NAME, USER, PASSWORD);
		} catch (Exception ex) {
			System.out.println("Connection was opened!");
		}
	}

	public Book getBookByIdAndChapter(int id, int chapter) {
		Book book = null;
		// SELECT COUNT(Book_ID) AS n_copies FROM book;
		String sql = "SELECT * FROM android_book.tblBook WHERE id = " + id + " AND chapter = " + chapter;
		connectiondb();
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String title = rs.getString("title");
				String author = rs.getString("author");
				book = new Book();
				book.id = id;
				book.title = title;
				book.author = author;
			}
			rs.close();
			stmt.close();
			con.close();
			return book;
		} catch (SQLException e) {
			System.out.println(e.toString());
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
}
